﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_Squared:DA_Matrix
    {
        //string xlsConnectStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
        string xlsConnectStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"" ";
        public DA_Squared()
        {
            tableName = "Squared";
        }

        protected bool setTableSchema = false;

        public DataTable GetSquaredAreaByLevel(string p_level)
        {
            return getSquaredAreaByLevel("SQUARED", p_level);
        }

        /// <summary>
        /// TEST 2011-09-19
        /// </summary>
        /// <param name="p_level"></param>
        /// <param name="p_nothing"></param>
        /// <returns></returns>
        public DataTable GetSquaredAreaByLevel(string p_level,string p_nothing)
        {
            return getSquaredAreaByLevel("SQUARED", p_level);
        }

        public bool Insert(string p_MISGroup, string p_MISItem, string p_Amount
            , string p_user_id, string p_row, string p_sheetName, string p_comment
            , bool p_l)
        {

            return true;
        }

        public bool Insert(string p_MISGroup, string p_MISItem, string p_Amount
            , string p_user_id, string p_row,string p_sheetName,string p_comment)
        {
            if (setTableSchema == false)
            {
                setTableSchema = true;
                FillTableSchema(tableName);
            }
            whereList = new List<string>();
            fieldList = new List<field>();
            string sql = @"
INSERT INTO Squared(MISGroup,MISItem,Amount,INIT_Time,user_id,ROW_POS,SHEETNAME,COMMENT) 
VALUES(@p_MISGroup,@p_MISItem,@p_Amount,GETDATE(),@p_user_id,@p_row_pos,@p_sheetName
,@p_comment)";
            FillFieldList(new string[] { p_MISGroup }, new string[] { "@p_MISGroup" }, "MISGroup");
            FillFieldList(new string[] { p_MISItem }, new string[] { "@p_MISItem" }, "MISItem");
            FillFieldList(new string[] { p_Amount }, new string[] { "@p_Amount" }, "Amount");
            FillFieldList(new string[] { p_user_id }, new string[] { "@p_user_id" }, "user_id");
            FillFieldList(new string[] { p_row }, new string[] { "@p_row_pos" }, "ROW_POS");
            FillFieldList(new string[] { p_sheetName }, new string[] { "@p_sheetName" }, "SHEETNAME");
            FillFieldList(new string[] { p_comment }, new string[] { "@p_comment" }, "COMMENT");
            AddParameter(sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult < 0 ? false : true;
        }

        /// <summary>
        /// for 2011 version
        /// </summary>
        public bool Insert(DataTable p_dt)
        {
            int iResult = Common.DAL.ExecuteNoneQuery(p_dt, "InsertSquared", "@Squared");
            return iResult < 0 ? false : true; 
        }


        /// <summary>
        /// 傳入兩個工作頁，來計算出差異
        /// </summary>
        /// <param name="p_before"></param>
        /// <param name="p_after"></param>
        /// <returns></returns>
        public DataTable GetSquaredDiff(string p_before,string p_after)
        {
            //before = 預算數 after = 實估數
            string sql = @"
SELECT
	A.MISGroup
	,A.MISItem
	,A.Amount - B.Amount AS DIFF

FROM( 
		SELECT 
		*	 
		FROM SQUARED A
		WHERE A.SHEETNAME = @p_before
	)A LEFT JOIN(
				SELECT 
				*	 
				FROM SQUARED A
				WHERE A.SHEETNAME = @p_after --稅前淨利彙總_實估數
				)B
ON A.MISGroup = B.MISGroup		
";
            ResetCriteria();
            FillTableSchema(tableName);

            return null;
        }

        /// <summary>
        /// 依不同的SHEET 取回自己的資料
        /// </summary>
        /// <param name="p_sheetName"></param>
        /// <param name="p_other"></param>
        /// <param name="p_after">不給空字串就是要取七點後的資料</param>
        /// <returns></returns>
        public DataTable GetDataBySheet(string p_sheetName, string p_other, string p_after)
        {

            string var1,after7;
            var1 = string.Empty;
            after7 = string.Empty.Equals(p_after) ? " 1=1 " : 
                string.Format("CONVERT(DATETIME,'{0}19:00:00',111)"
                ,DateTime.Now.ToShortDateString());
            if (!string.Empty.Equals(p_other))
            {
                var1 = string.Format(@"
UNION ALL

SELECT 
		0
		,A.MISItem
		,SUM(A.AMOUNT) AS TOT
		,'{0}'
		,CASE 
			WHEN A.MISItem = '銀行' THEN 'C13'
			WHEN A.MISItem = '證券' THEN 'E13'
			WHEN A.MISItem = '人壽' THEN 'G13'
            WHEN A.MISItem = '產險' THEN 'I13'
            WHEN A.MISItem = 'AMC' THEN 'K13'
            WHEN A.MISItem = '92%創投' THEN 'M13'
            WHEN A.MISItem = '管顧' THEN 'O13'
			WHEN A.MISItem = '金控' THEN 'S13'
			WHEN A.MISItem = '直效' THEN 'Q13'
            WHEN A.MISItem = '75%FBHK' THEN 'U13'
            WHEN A.MISItem = '51%運彩' THEN 'W13'
            WHEN A.MISItem = '40%投信' THEN 'Y13'
        END AS MISGROUP
		,''
FROM
(SELECT
	*
	,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.MISItem,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
FROM Squared AS A 
WHERE A.SHEETNAME = '{1}'
AND {2}
AND MISGroup NOT IN ('C42','C43','S42','S43','E42','E43','E45','E46','E47','E48','E49')
)A
WHERE A.SEQ= 1
GROUP BY A.MISItem	
", p_sheetName, p_other,after7);
            }

            string sql =string.Format(@"
SELECT 
		A.ROW_POS
		,CASE 
            WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
            THEN 0
            WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
            THEN 0
            ELSE A.AMOUNT
         END  AMOUNT
        --,A.AMOUNT
		,A.SHEETNAME
		,A.MISGroup
        ,A.COMMENT
	FROM(
		SELECT
			*
			,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
        WHERE A.SHEETNAME = '{0}'
        AND {2}
		)A
	WHERE A.SEQ = 1	
	
    {1}

", p_sheetName,var1,after7);
            return getData(sql, p_sheetName);
        }       

        public DataTable GetDataBySheet(string p_sheetName,string p_after)
        {
            string sql = @"
SELECT * 
FROM (
    SELECT *
        ,ROW_NUMBER() 
            OVER(PARTITION BY SheetName,Team,Pos ORDER BY INIT_Time DESC) AS SEQ
    FROM Squared2011 
)A WHERE A.SEQ = 1
";
            return null;
        }

        public DataTable GetBudgetData()
        {
            string sql = @"
SELECT 
	A.ROW_POS
	,A.cell_value
	,A.SHEETNAME
	,A.MISGroup
FROM(
	SELECT
		*
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup ORDER BY A.INIT_Time DESC) AS SEQ
	FROM bonus AS A 
    WHERE A.SHEETNAME IN (SELECT DISTINCT A.fldname 
                            FROM MISParameter A
                            WHERE 1=1 
                                AND (team = 'budget') 
                           )           
	)A
WHERE A.SEQ = 1	
ORDER BY sheetname,row_pos
";
            return getData(sql,"budget");
        }

        /// <summary>
        /// 用來在查詢的那頁顯示目前最後更新的版本
        /// </summary>
        /// <returns></returns>
        public DataSet GetLastUpload()
        {
            string[] shts = { "稅前淨利彙總_預算數"
                                ,"稅前淨利彙總_實估數" };
            DataTable dt = null;
            DataSet ds = new DataSet();
            string sql = string.Empty;
            foreach (string sht in shts)
            {
                sql = lastUploadStatement(sht);
                dt = getData(sql, sht);
                ds.Tables.Add(dt);
            }
            return ds;
        }

        protected string lastUploadStatement(string p_sht)
        {
            return string.Format(
@"SELECT * 
FROM
(
	SELECT
	(SELECT B.TEAM FROM MISTEAM B WHERE B.USER_ID = A.user_id) TEAM
	,MAX(A.INIT_Time) MM
	FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER
			(
				PARTITION BY 
					A.ROW_POS,A.MISGroup
					,A.MISItem,A.SHEETNAME 
				ORDER BY A.INIT_Time DESC
			) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '{0}'
	)A 
	WHERE SEQ = 1
	GROUP BY A.user_id
)A ORDER BY A.MM DESC
"
,p_sht)
;
        }

        public DataTable GetSquaredData()
        {
            #region sql
            /*
            string sql = @"
SELECT 
	A.MISGroup
	,CASE 
		WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
		THEN 0
		WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
		THEN 0
		ELSE A.AMOUNT
	END  AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
        
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME IN ('其它收支明細_預算數'
		,'其它收支明細_實估數','稅前淨利彙總_差異說明','其它收支明細_差異說明'
		,'102年手續收入','101年手續收入','101年利息收入','102年利息收入'
		,'稅前淨利彙總_實估數','稅前淨利彙總_預算數')
        --AND INIT_Time BETWEEN CAST('2012-09-10' AS DATE) AND CAST('2013-03-09' AS DATE)
		AND  1=1 )A
WHERE A.SEQ = 1	0
";*/
            
            string sql = @"
SELECT * FROM (
SELECT 
	A.MISGroup
	,CASE 
		WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
		THEN 0
		WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
		THEN 0
		ELSE A.AMOUNT
	END  AMOUNT
	,A.SHEETNAME
	,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
	FROM Squared AS A 
    WHERE A.SHEETNAME = '稅前淨利彙總_實估數'
    AND  1=1)A
WHERE A.SEQ = 1	

UNION ALL

SELECT 
	A.MISGroup
	,CASE 
		WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
		THEN 0
		WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
		THEN 0
		ELSE A.AMOUNT
	END  AMOUNT
	,A.SHEETNAME
	,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
	FROM Squared AS A 
    WHERE A.SHEETNAME = '稅前淨利彙總_預算數'
    AND  1=1)A
WHERE A.SEQ = 1	

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
        
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '其它收支明細_預算數'
		AND  1=1 )A
WHERE A.SEQ = 1	

UNION ALL
	
SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '其它收支明細_實估數'
		AND  1=1 )A
WHERE A.SEQ = 1	    

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '稅前淨利彙總_差異說明'
		AND  1=1 )A
WHERE A.SEQ = 1	 

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '其它收支明細_差異說明'
        AND  1=1 )A
WHERE A.SEQ = 1	    

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '103年利息收入'
        AND  1=1 )A
WHERE A.SEQ = 1	    

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '103年手續費收入'
		AND  1=1 )A
WHERE A.SEQ = 1	    

UNION ALL

SELECT 
	A.MISGroup
	,A.AMOUNT
	,A.SHEETNAME
    ,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM Squared AS A 
		WHERE A.SHEETNAME = '103年股利收入'
        AND  1=1 )A
WHERE A.SEQ = 1	    
)A ORDER BY SHEETNAME,MISGroup
";
            #endregion
            return getData(sql, "squared");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public void FillSquaredData(string p_xls)
        {
            string sql,cnStr;            
            cnStr =  string.Format(xlsConnectStr,p_xls);
            //sql = "INSERT INTO [Download$a11:c600](fld1,fld2,fld3) values('{0}','{1}','{2}')";
            //sql = "INSERT INTO [Download$a11:c600] values('{0}','{1}','{2}')";
            sql = "UPDATE [Download$a11:c600] SET CELL_VALUE = {2} WHERE SHEETNAME = '{0}' AND MISGROUP ='{1}'";
            using (System.Data.OleDb.OleDbConnection cn
                = new System.Data.OleDb.OleDbConnection(cnStr))
            {
                cn.Open();
                System.Data.OleDb.OleDbCommand cmd = cn.CreateCommand();
                DataTable dt = getSquaredData2();
                foreach (DataRow row in dt.Rows)
                {
                    cmd.CommandText = string.Format(sql
                        , row[0].ToString(), row[1].ToString()
                        , string.IsNullOrEmpty(row[2].ToString())?"0" 
                            : row[2].ToString()
                        );
                    cmd.ExecuteNonQuery();
                }
                dt = getSquaredWith1819();
                //sql = "INSERT INTO [Download$f11:i{4}] values('{0}','{1}','{2}','{3}')";
                sql = "UPDATE [Download$f11:i100] SET M18={2},M19={3} WHERE MG='{0}' AND SHEETNAME='{1}'";
                foreach (DataRow row in dt.Rows)
                {
                    cmd.CommandText = string.Format(sql
                        , row[0].ToString(), row[1].ToString()
                        , row[2].ToString(), row[3].ToString());
                    cmd.ExecuteNonQuery();
                }

                //--填入版本日
                cmd.CommandText = string.Format("UPDATE [Download$d1:d2] SET version_date='{0}'"
                    , DateTime.Now.ToShortDateString());
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 新增資料從excel
        /// </summary>
        /// <param name="p_xls">Excel檔案路徑</param>
        /// <param name="p_level">群組</param>
        /// <param name="p_dt">空的 Bonus Table</param>
        /// <param name="p_id">上傳者的ID</param>
        /// <param name="p_team">Misparameter.team</param>
        /// <returns></returns>
        public bool InsertFromXls(string p_xls,string p_level,string p_id
            ,DataTable p_dt,string p_team)
        {
            string cnStr,sql;
            cnStr = string.Format(xlsConnectStr, p_xls);
            //sql = "SELECT * FROM [UPLOAD$A11:D200] ";
            sql = string.Format("SELECT * FROM [UPLOAD_{0}$A11:D200] WHERE sheetname <> NULL ", p_team);
            IEnumerable<DataRow> selectRow = null;
            bool result = false;

            try
            {
                DataTable rangeDT = GetUserExcelRange(p_team,p_level);
                using (System.Data.OleDb.OleDbConnection cn
                   = new System.Data.OleDb.OleDbConnection(cnStr))
                {
                    cn.Open();
                    System.Data.OleDb.OleDbCommand cmd = cn.CreateCommand();
                    cmd.CommandText = sql;
                    DataTable xlsDT = new DataTable();
                    xlsDT.Load(cmd.ExecuteReader());
                    foreach (DataRow row in rangeDT.Rows)
                    {
                        selectRow = from aa in xlsDT.AsEnumerable()
                                    where (row[0].ToString().Equals(aa.Field<string>("sheetname").ToString())
                                          & row[1].ToString().Contains(
                                          aa.Field<string>("misgroup").ToString()))
                                    select aa;
                        if (selectRow.Count().Equals(0))
                        {
                            throw new Exception("使用者沒有這個Cell！！");
                        }
                        foreach (DataRow xsRow in selectRow)
                        {                                
                            DataRow bonusRow = p_dt.NewRow();
                            bonusRow["misgroup"] = xsRow["misgroup"].ToString();
                            bonusRow["cell_value"] = xsRow["cell_value"].ToString();
                            bonusRow["user_id"] = p_id;
                            bonusRow["row_pos"] = xsRow["row_pos"].ToString();
                            bonusRow["sheetname"] = xsRow["sheetname"].ToString();
                            p_dt.Rows.Add(bonusRow);
                        }
                    }
                    cn.Close();
                }
                int iResult = Common.DAL.ExecuteNoneQuery(p_dt, "InsertBonus", "@bonus");
                result = iResult < 0 ? false : true; 
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        protected DataTable getSquaredData2()
        {
            string sql = @"
SELECT * FROM (
SELECT 
	A.SHEETNAME
    ,A.MISGroup
	,A.CELL_VALUE
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
	FROM BONUS AS A 
    WHERE A.SHEETNAME IN ('稅前淨利彙總_實估數','稅前淨利彙總_預算數')
    --AND INIT_Time BETWEEN CAST('2012-09-10' AS DATE) AND CAST('2013-03-09' AS DATE)
    AND INIT_Time > CAST('2012-03-10' AS DATE)
	AND (ROW_POS < 18 OR ROW_POS > 19)
    AND  1=1)A
WHERE A.SEQ = 1	

UNION ALL

SELECT 
	A.SHEETNAME
    ,A.MISGroup
	,A.CELL_VALUE
        
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
		FROM BONUS AS A 
		WHERE A.SHEETNAME IN ('其它收支明細_預算數'
		,'其它收支明細_實估數','稅前淨利彙總_差異說明','其它收支明細_差異說明'
		,'102年手續收入','101年手續收入','101年利息收入','102年利息收入')
        --AND INIT_Time BETWEEN CAST('2012-09-10' AS DATE) AND CAST('2013-03-09' AS DATE)
		AND  1=1 )A
WHERE A.SEQ = 1	

)A ORDER BY SHEETNAME,MISGroup
";
            return getData(sql, "squared");
        }

        /// <summary>
        /// 只取18、19 這兩個row的資料，因為要特別計算
        /// </summary>
        /// <returns></returns>
        protected DataTable getSquaredWith1819()
        {
            /*
            string sql = @"
SELECT
	mg	
	,SHEETNAME
	,MAX(CASE WHEN MISGroup LIKE '%18%' THEN AMOUNT ELSE 0 END) M18
	,MAX(CASE WHEN MISGroup LIKE '%19%' THEN AMOUNT ELSE 0 END) M19
FROM(

SELECT 
	left(A.MISGroup,2)mg
	,A.MISGroup
	,CASE 
		WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
		THEN 0
		WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
		THEN 0
		ELSE A.AMOUNT
	END  AMOUNT
	,A.SHEETNAME
	,A.COMMENT
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
	FROM Squared AS A 
    WHERE A.SHEETNAME IN ('稅前淨利彙總_預算數','稅前淨利彙總_實估數')
    AND INIT_Time BETWEEN CAST('2012-09-10' AS DATE) AND CAST('2013-03-09' AS DATE)
    AND ROW_POS IN (18,19)
    AND  1=1)A
WHERE A.SEQ = 1	
)A GROUP BY mg,SHEETNAME
";
             */
            string sql = @"
SELECT
	mg	
	,SHEETNAME
	,MAX(CASE WHEN MISGroup LIKE '%18%' THEN AMOUNT ELSE '0' END) M18
	,MAX(CASE WHEN MISGroup LIKE '%19%' THEN AMOUNT ELSE '0' END) M19
FROM(

SELECT 
	left(A.MISGroup,2)mg
	,A.MISGroup
	,CASE 
		WHEN MISGroup = 'E19' AND SHEETNAME = '稅前淨利彙總_預算數' AND USER_ID = 'may.ho'
		THEN '0'
		WHEN MISGroup = 'E18' AND SHEETNAME = '稅前淨利彙總_實估數' AND USER_ID = 'may.ho'
		THEN '0'
		ELSE A.CELL_VALUE
	END  AMOUNT
	,A.SHEETNAME
FROM(SELECT *
		,ROW_NUMBER() OVER(PARTITION BY A.ROW_POS,A.MISGroup,A.SHEETNAME ORDER BY A.INIT_Time DESC) AS SEQ
	FROM bonus AS A 
    WHERE A.SHEETNAME IN ('稅前淨利彙總_預算數','稅前淨利彙總_實估數')
    --AND INIT_Time BETWEEN CAST('2012-09-10' AS DATE) AND CAST('2013-03-09' AS DATE)
    AND ROW_POS IN (18,19)
    AND  1=1)A
WHERE A.SEQ = 1	
)A GROUP BY mg,SHEETNAME
";
            return getData(sql, "squared1819");
        }

        /// <summary>
        /// 傳回空的資料表格
        /// </summary>
        /// <returns></returns>
        public DataTable GetSquaredDT()
        {
            DataTable dt = new DataTable("dt");
            dt.Columns.Add(new DataColumn("MISGroup",System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("MISItem", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Amount", System.Type.GetType("System.Decimal")));
            dt.Columns.Add(new DataColumn("user_id", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("ROW_POS", System.Type.GetType("System.Int16")));
            dt.Columns.Add(new DataColumn("SHEETNAME", System.Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("COMMENT", System.Type.GetType("System.String")));
            return dt;
        }

        /// <summary>
        /// 取得user 自己的位置
        /// </summary>
        /// <param name="p_user"></param>
        /// <param name="p_pwd"></param>
        /// <returns></returns>
        public DataTable GetUserExcelRange(string p_1, string p_2)
        {
            #region sql
            /*
            string sql = string.Format(@"
SELECT 
	fldname,
	fldvalue FROM MISParameter WHERE team = 'SQUARED'
AND para1 = (SELECT SEQUARED_LEVEL 
	FROM MISUser WHERE user_id = '{0}' 
	AND user_pw = '{1}')
ORDER BY seq
",p_user,p_pwd);
            */

            p_1 = string.IsNullOrEmpty(p_1) ? "SQUARED" :p_1;
            string sql = string.Format(@"
SELECT fldname,	fldvalue FROM MISParameter WHERE para1 ='{0}'
AND team = '{1}' ORDER BY seq", p_2,p_1);

            #endregion
            return getData(sql, "squaredLevel");

        }

        /// <summary>
        /// 讓EXCEL先download回去，在前端設定
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllCell()
        {
            return getSquaredData2();
        }

        /// <summary>
        /// 讓EXCEL先download回去，在前端設定
        /// </summary>
        /// <returns></returns>
        public DataTable Get1819Cell()
        {
            return getSquaredWith1819();
        }
    }
}
